﻿using CuaHangLego.Bussiness.Parser;
using CuaHangLego.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CuaHangLego.DataAccess
{
    public class KhachHangDA : DABase, IDA<KhachHang>
    {
        public static KhachHangDA Instance = new KhachHangDA();

        public int Insert(KhachHang item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("INSERT INTO KHACHHANG ({0},{1},{2},{3},{4},{5}) VALUES(N'{6}',N'{7}','{8}','{9}','{10}',N'{11}'); SELECT CAST(scope_identity() AS int);",
                "HoTen", "DiaChi", "DienThoai", "CMND", "Email", "SoThich",
                item.HoTen, item.DiaChi, item.DienThoai, item.CMND, item.Email, item.SoThich);
            this.OpenDB();
            int rs = int.Parse(ExecuteQuerySingleValue(sql).ToString());
            this.CloseDB();
            return rs;
        }

        public bool Update(KhachHang item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE KHACHHANG SET {0}=N'{6}', {1}=N'{7}', {2}='{8}', {3}='{9}', {4}='{10}', {5}=N'{11}' WHERE ID={12};",
                "HoTen", "DiaChi", "DienThoai", "CMND", "Email", "SoThich",
                item.HoTen, item.DiaChi, item.DienThoai, item.CMND, item.Email, item.SoThich, item.MaSo);
            OpenDB();
            int rs = int.Parse(ExecuteQuery(sql).ToString());
            CloseDB();
            return rs > 0;
        }

        public bool Delete(KhachHang item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("DELETE FROM KHACHHANG WHERE {0}={1}", "ID", item.MaSo);
            OpenDB();
            int rs = ExecuteQuery(sql);
            CloseDB();
            return rs > 0;
        }

        public IList<KhachHang> GetAll()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM KHACHHANG");
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<KhachHang> list = new List<KhachHang>();
            while (reader.Read())
            {
                list.Add(KhachHangParser.Parse(reader));
            }
            CloseDB();
            return list;
        }

        public KhachHang GetByID(int ID)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("SELECT * FROM KHACHHANG WHERE ID={0};", ID);
            OpenDB();
            SqlDataReader rs = ExecuteQueryReader(sql);
            KhachHang k = rs.Read() == true ? KhachHangParser.Parse(rs) : null;
            CloseDB();
            return k;
        }

        public int GetIDLogin(string username, string password)
        {
            int ID = -1;
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("SELECT ID FROM KHACHHANG WHERE TenDangNhap='{0}' AND MatKhau='{1}';", username, password);
            OpenDB();
            SqlDataReader rs = ExecuteQueryReader(sql);
            ID = rs.Read() == true ? int.Parse(rs["ID"].ToString()) : -1;
            CloseDB();
            return ID;
        }

        public bool CheckExistUsername(string username)
        {
            // Su dung cau truy van de kiem tra xem username da duoc dang ky chua
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("SELECT COUNT(*) FROM KHACHHANG WHERE TenDangNhap='{0}';", username);
            OpenDB();
            int rs = int.Parse(ExecuteQuerySingleValue(sql).ToString());
            CloseDB();
            return rs > 0;
        }

        public bool UpdateAccount(int ID, TaiKhoan t)
        {
            // Su dung cau truy van de update ten tai khoan va mat khau
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE KHACHHANG SET {0}='{2}', {1}='{3}' WHERE ID={4};",
                "TenDangNhap", "MatKhau",
                t.Username, t.Password,
                ID);
            OpenDB();
            int rs = int.Parse(ExecuteQuery(sql).ToString());
            CloseDB();
            return rs > 0;
        }

        public bool ChangePassword(int ID, string newPassword)
        {
            // Su dung cau truy van de update ten tai khoan va mat khau
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE KHACHHANG SET {0}='{1}' WHERE ID={2};",
                "MatKhau", newPassword, ID);
            OpenDB();
            int rs = int.Parse(ExecuteQuery(sql).ToString());
            CloseDB();
            return rs > 0;
        }

        public bool CheckPassword(int ID, string Password)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("SELECT COUNT(*) FROM KHACHHANG WHERE ID={0} AND MatKhau='{1}';", ID, Password);
            OpenDB();
            int rs = int.Parse(ExecuteQuerySingleValue(sql).ToString());
            CloseDB();
            return rs > 0;
        }
    }
}
